Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tuning Considerations

The DSS system is tuned to allow several large processes to run at maximum throughput. There is little concern for response times. All the tuning tips presented in Part II of this book, “Tuning the Server,” apply here. Remember that with the DSS system, much less concern is given to user-response times; throughput counts.

You may have to tune both Oracle and the server operating system. The following sections look first at Oracle and then at the server operating system.

Oracle Tuning

Carefully analyze these things to determine whether adjustment to your Oracle parameters is necessary:

  DB_BLOCK_BUFFERS. In the DSS system, block size is not nearly as critical as it is in OLTP and batch systems. However, having an insufficient number of block buffers results in higher-than-normal I/O rates and possibly an I/O bottleneck. The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The ratio of PHYSICAL READS to DB BLOCK GETS and CONSISTENT GETS is the cache-miss ratio. This number should be minimized.
  Library cache. Remember to check The V$LIBRARYCACHE table that contains statistics about how well you are using the library cache. The important columns to view in this table are PINS and RELOADS. A low number of reloads relative to the number of executions indicates a high cache-hit rate. You can reduce the library cache misses by increasing the amount of memory available for the library cache. Do this by increasing the Oracle tunable parameter SHARED_POOL_SIZE. Try increasing the size of the shared pool by 10 percent and monitor it again. If this is not sufficient, increase the size of the shared pool by another 10 percent until you are pleased with the performance.
  Multiblock reads. Because many DSS queries involve table scans, make sure that you can take advantage of multiblock reads. The number of blocks read in a multiblock read is specified by DB_FILE_MULTIBLOCK_READ_COUNT, an Oracle initialization parameter. This value, multiplied by the DB_BLOCK_SIZE parameter, results in the size of the I/Os. A good value for the size of a multiblock read I/O is 64K.
  Cursor space for time. If you have plenty of memory, you can speed access to the shared SQL areas by setting the CURSOR_SPACE_FOR_TIME Oracle initialization parameter to TRUE.
  Data dictionary cache. To check the efficiency of the data dictionary cache, look at the dynamic performance table V$ROWCACHE. The important columns to view in this table are GETS and GETMISSES. The ratio of GETMISSES to GETS should be low.
  Rollback segments. Because of the limited number of updates in a DSS system, rollback contention and the number of rollback segments are usually not important.
  Latch contention. Because of the limited number of updates in a DSS system, latches are also not an issue.
  Checkpoints. The effect of the checkpoint is not an issue in a typical DSS system because user response times are not critical.
  Archiving. As with the other log-related parameters, the limited updates in a DSS system mean that archiving is not an issue.

Pay particular attention to these areas when tuning a system for decision support. Probably the area that requires the most attention is I/O and memory because these two are so closely related. By optimizing the use of memory, you may be able to reduce I/Os (which are probably running near the limitations of the hardware).

Server OS Tuning

You may have to tune the server OS to provide optimal I/O performance. Some of the things you may have to tune in the server OS are listed here; remember that some OSes may not require any tuning in these areas:

  Memory. Tune the system to reduce unnecessary memory usage so that Oracle can use as much of the system’s memory as possible for the SGA and server processes. You may also need significant amounts of memory for sorts.
  Memory enhancements. Take advantage of 4M pages and ISM, if they are available. Both features can improve Oracle performance in a DSS environment.
  I/O. If necessary, tune I/O to allow for optimal performance and use of AIO.
  Scheduler. If possible, turn off preemptive scheduling and load balancing. In a DSS system, allowing a process to run to completion (that is, so that it is not preempted) is beneficial.
  Cache affinity. You may see some benefits from cache affinity in a DSS system because the processes tend to run somewhat longer.

The server operating system is mainly a host on which Oracle does its job. Any work done by the operating system is essentially overhead for Oracle. By optimizing code paths and reducing OS overhead, you can enhance Oracle performance.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.